package com.joysuch.util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public class JdbcUtil {
    private static final Connection connection;

    static {
        String url = "jdbc:mysql://124.221.76.174:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
        String user = "root";
        String password = "gwl666";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void createTable(Class<?> T){
        String createTableSql = getCreateTableSql(T);
    }

    public static <T> List<T> query(Class<T> clazz, String sql) {
        PreparedStatement preparedStatement = null;
        List<T> list = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet != null) {
                while (resultSet.next()) {
                    T entity = clazz.newInstance();
                    for (Field field : fields) {
                        field.setAccessible(true);
                        field.set(entity, resultSet.getObject(field.getName()));
                    }
                    list.add(entity);
                }
            }
        } catch (SQLException | InstantiationException | IllegalAccessException e) {
            throw new RuntimeException(e);
        } finally {
            close(resultSet, preparedStatement);
        }
        return list;
    }

    public static <T> boolean insert(T data, Class<T> clazz) {
        boolean flag = false;
        String tableName = clazz.getSimpleName();
        Field[] fields = clazz.getDeclaredFields();
        try {
            Object entity = clazz.newInstance();
            StringBuilder insertSqlBuilder = new StringBuilder("INSERT INTO `"+ tableName +"` (");
            StringBuilder fieldBuilder = new StringBuilder();
            StringBuilder dataBuilder = new StringBuilder();
            Map<Integer, Object> dataMap = new HashMap<>();
            Integer index = 1;
            for (Field field : fields) {
                field.setAccessible(true);
                dataMap.put(index, field.get(data));
                insertSqlBuilder.append("`").append(field.getName()).append("`,");
                dataBuilder.append("?,");
                index++;
            }
            String insertSql = insertSqlBuilder.substring(0, insertSqlBuilder.length() - 1) + ") values (" + dataBuilder.substring(0, dataBuilder.length() - 1) + ")";
            PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
            for (int i = 1; i <= fields.length; i++) {
                preparedStatement.setObject(i, dataMap.get(i));
            }
            preparedStatement.execute();

        } catch (InstantiationException | IllegalAccessException | SQLException e) {
            throw new RuntimeException(e);
        }
        return flag;
    }

    private static String getCreateTableSql(Class<?> clazz) {
        Map<String, String> fieldTypeMap = getSqlFieldType();
        String tableName = clazz.getSimpleName();
        Field[] fields = clazz.getDeclaredFields();
        String sqlFieldType;
        StringBuilder createTableSqlBuilder = new StringBuilder("CREATE TABLE IF NOT EXISTS `"+ tableName +"` (");
        for (Field field : fields) {
            sqlFieldType = null;
            String fieldName = field.getName();
            if (Objects.equals(fieldName, "id")){
                sqlFieldType = "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY";
            }else {
                sqlFieldType = fieldTypeMap.get(field.getType().toString());
            }
            createTableSqlBuilder.append("`").append(fieldName).append("` ").append(sqlFieldType).append(",");
        }
        return createTableSqlBuilder.substring(0, createTableSqlBuilder.length() - 1) + ")";
    }

    private static Map<String, String> getSqlFieldType(){
        Map<String, String> fieldMap = new HashMap<>();
        fieldMap.put("class java.lang.Integer", "int(11)");
        fieldMap.put("int", "int(11) DEFAULT NULL");
        fieldMap.put("class java.lang.String", "varchar(255) DEFAULT NULL");
        fieldMap.put("class java.math.BigDecimal", "decimal(18,12) DEFAULT NULL");
        fieldMap.put("class java.lang.Long", "bigint");
        return fieldMap;
    }

    private static void close(ResultSet resultSet, Statement statement) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        if (statement != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        if (JdbcUtil.connection != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
